﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Web;
using System.Web.Caching;

using CoreLab.Oracle; //using Devart.Data.Oracle; //using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;

using LMD.Constant;
using LMD.Lib.Oracle;
using LMD.Lib.DataClass;

namespace Cached
{
    /// <summary>
    /// Summary description for NewsFeed
    /// </summary>
    public class NewsFeedTable : CacheAbstraction
    {
        String sxFilterHierarki_Sales =
            @"
            and 
            (
                v1.CUSTOMER_ID is null
                or v1.CUSTOMER_ID IN
                (
                    select customer_id
                    from V_ICP_COMPANY_CAR_SALES
                    where SALES_EMPLOYEE_NO IN
                    (
                        select :prmUID from dual
                        UNION ALL
                        select EMPLOYEE_NO from slogix.p_slx_usergroup_member where p_slx_usergroup_id IN
                        (
                            select 
                                p_slx_usergroup_id 
                            from 
                                slogix.p_slx_usergroup
                            start with 
                                p_slx_usergroup_id =
                                (
                                    select max(a)
                                    from 
                                    (
                                        select 
                                            decode(ishead, 'Y', p_slx_usergroup_id, -1) a
                                        from 
                                            slogix.p_slx_usergroup_member 
                                        where EMPLOYEE_NO = :prmUID
                                    )
                                ) 
                            connect by prior 
                                p_slx_usergroup_id = parent_id
                        )
                    )
                )
            )";

        String sxFilterHierarki_Sales3 =
            @"
            where NF.SALES_EMPLOYEE_NO IN
            (
                select :prmUID from dual
                UNION ALL
                select EMPLOYEE_NO from slogix.p_slx_usergroup_member where p_slx_usergroup_id IN
                (
                    select 
                        p_slx_usergroup_id 
                    from 
                        slogix.p_slx_usergroup
                    start with 
                        p_slx_usergroup_id =
                        (
                            select max(a)
                            from 
                            (
                                select 
                                    decode(ishead, 'Y', p_slx_usergroup_id, -1) a
                                from 
                                    slogix.p_slx_usergroup_member 
                                where EMPLOYEE_NO = :prmUID
                            )
                        ) 
                    connect by prior 
                        p_slx_usergroup_id = parent_id
                )
            )
            ";

        String sxFilterHierarki_CAR =
            @"
            and 
            (
                v1.CUSTOMER_ID is null
                or v1.CUSTOMER_ID IN
                (
                    select customer_id
                    from V_ICP_COMPANY_CAR_SALES
                    where CAR_EMPLOYEE_NO IN
                    (  
                        select :prmUID from dual
                        UNION ALL          
                        select 
                            NIK 
                        from 
                            v_icp_escalation
                        start with 
                            NIK = :prmUID
                        connect by prior 
                            t_qrtz_user_destination_id = parent_id
                    )
                )
            )";

        String sxFilterHierarki_CAR3 =
            @"
                where NF.CAR_EMPLOYEE_NO IN 
                (
                    select :prmUID from dual
                    UNION ALL          
                    select 
                        NIK 
                    from 
                        v_icp_escalation
                    start with 
                        NIK = :prmUID
                    connect by prior 
                        t_qrtz_user_destination_id = parent_id
                )
            ";
			
        public NewsFeedData[] GetUserNewsFeedList(String sxUserId)
        {
            DataTable dt = GetUserNewsFeedTable(sxUserId);
            return ConvertTableToObjectArray(dt);
        }
        private DataTable GetUserNewsFeedTable(String sxUserId)
        {
            DBLib dbl = new DBLib();

            OracleParameter prm = new OracleParameter();
            prm.ParameterName = "prmID";
            prm.OracleDbType = OracleDbType.VarChar;
            prm.Value = sxUserId;

            String sxQuery =
            @"
            select * from 
            (
                select
                    T_NEWS_FEED_ID,
                    CONTENT,
                    TITLE,
                    POSTING_USER_ID,
                    POSTING_USER_NAME,
                    POSTED_USER_ID,
                    POSTED_USER_NAME,
                    CUSTOMER_ID,    
                    COMPANY_NAME,
                    SOURCE_NAME,
                    SOURCE_URL,
                    POST_DATE,     
                    NEWS_FEED_DATA,
                    P_NEWS_FEED_DATA_TYPE_ID,
                    NEWS_FEED_DATA_TYPE,
                    COMMENT_COUNT 
                from
                    V_ICP_NEWS_FEED
                where
                    POSTING_USER_ID = :prmID
                    or POSTED_USER_ID = :prmID
                order by 
                    T_NEWS_FEED_ID desc
            )
            where rownum < " + KGlobal.DEFAULT_FEED_COUNT;
            DataTable dt = dbl.GetDataTable(sxQuery, prm);
            
            return dt;
        }

        public NewsFeedData[] GetUserNewsFeedUpdateList(String sxUserId, int iNewestId)
        {
            DataTable dt = GetUserNewsFeedUpdateTable(sxUserId, iNewestId);
            return ConvertTableToObjectArray(dt);
        }
        private DataTable GetUserNewsFeedUpdateTable(String sxUserId, int iNewestId)
        {
            DBLib dbl = new DBLib();

            OracleParameter prm = new OracleParameter();
            prm.ParameterName = "prmID";
            prm.OracleDbType = OracleDbType.Number;
            prm.Value = iNewestId;

            OracleParameter prm2 = new OracleParameter();
            prm2.ParameterName = "prmUserID";
            prm2.OracleDbType = OracleDbType.VarChar;
            prm2.Value = sxUserId;

            String sxQuery =
            @"
            select
                T_NEWS_FEED_ID,
                CONTENT,
                TITLE,
                POSTING_USER_ID,
                POSTING_USER_NAME,
                POSTED_USER_ID,
                POSTED_USER_NAME,
                CUSTOMER_ID,    
                COMPANY_NAME,
                SOURCE_NAME,
                SOURCE_URL,
                POST_DATE,     
                NEWS_FEED_DATA,
                P_NEWS_FEED_DATA_TYPE_ID,
                NEWS_FEED_DATA_TYPE,
                COMMENT_COUNT 
            from
                V_ICP_NEWS_FEED
            where
                ( 
                    POSTING_USER_ID = :prmUserID
                    or POSTED_USER_ID = :prmUserID
                )
                and T_NEWS_FEED_ID > :prmID
            order by 
                T_NEWS_FEED_ID desc";

            DataTable dt = dbl.GetDataTable(sxQuery, prm, prm2);
            
            return dt;
        }

        public NewsFeedData[] GetUserNewsFeedOlderList(String sxUserId, int iOldestId)
        {
            DataTable dt = GetUserNewsFeedOlderTable(sxUserId, iOldestId);
            return ConvertTableToObjectArray(dt);
        }
        private DataTable GetUserNewsFeedOlderTable(String sxUserId, int iOldestId)
        {
            DBLib dbl = new DBLib();

            OracleParameter prm = new OracleParameter();
            prm.ParameterName = "prmID";
            prm.OracleDbType = OracleDbType.Number;
            prm.Value = iOldestId;

            OracleParameter prm2 = new OracleParameter();
            prm2.ParameterName = "prmUserID";
            prm2.OracleDbType = OracleDbType.VarChar;
            prm2.Value = sxUserId;

            String sxQuery =
            @"
            select * from
            (
                select
                    T_NEWS_FEED_ID,
                    CONTENT,
                    TITLE,
                    POSTING_USER_ID,
                    POSTING_USER_NAME,
                    POSTED_USER_ID,
                    POSTED_USER_NAME,
                    CUSTOMER_ID,    
                    COMPANY_NAME,
                    SOURCE_NAME,
                    SOURCE_URL,
                    POST_DATE,     
                    NEWS_FEED_DATA,
                    P_NEWS_FEED_DATA_TYPE_ID,
                    NEWS_FEED_DATA_TYPE,
                    COMMENT_COUNT 
                from
                    V_ICP_NEWS_FEED
                where
                    ( 
                    POSTING_USER_ID = :prmUserID
                    or POSTED_USER_ID = :prmUserID
                    )
                    and T_NEWS_FEED_ID < :prmID
                order by 
                    T_NEWS_FEED_ID desc
            )
            where rownum < " + KGlobal.DEFAULT_FEED_COUNT;
            DataTable dt = dbl.GetDataTable(sxQuery, prm, prm2);
            
            return dt;
        }

        public NewsFeedData[] GetUserNewsFeedFilteredList(String sxUserId, String sxFilter)
        {
            DataTable dt = GetUserNewsFeedFilteredTable(sxUserId, sxFilter);
            return ConvertTableToObjectArray(dt);
        }
        private DataTable GetUserNewsFeedFilteredTable(String sxUserId, String sxFilter)
        {
            DBLib dbl = new DBLib();

            OracleParameter prm = new OracleParameter();
            prm.ParameterName = "search";
            prm.OracleDbType = OracleDbType.VarChar;
            prm.Value = sxFilter;

            OracleParameter prm2 = new OracleParameter();
            prm2.ParameterName = "prmUserID";
            prm2.OracleDbType = OracleDbType.VarChar;
            prm2.Value = sxUserId;

            String sxQuery =
            @"
            select * from
            (
                select
                    T_NEWS_FEED_ID,
                    CONTENT,
                    TITLE,
                    POSTING_USER_ID,
                    POSTING_USER_NAME,
                    POSTED_USER_ID,
                    POSTED_USER_NAME,
                    CUSTOMER_ID,    
                    COMPANY_NAME,
                    SOURCE_NAME,
                    SOURCE_URL,
                    POST_DATE,     
                    NEWS_FEED_DATA,
                    P_NEWS_FEED_DATA_TYPE_ID,
                    NEWS_FEED_DATA_TYPE,
                    COMMENT_COUNT 
                from 
                    V_ICP_NEWS_FEED
                where 
                    ( 
                        POSTING_USER_ID = :prmUserID
                        or POSTED_USER_ID = :prmUserID
                    )
                    and 
                    (
                        UPPER(CONTENT) like '%' || UPPER(:search) || '%'
                        or UPPER(TITLE) like '%' || UPPER(:search) || '%'
                        or UPPER(POSTING_USER_NAME) like '%' || UPPER(:search) || '%'
                        or UPPER(POSTED_USER_NAME) like '%' || UPPER(:search) || '%'
                        or UPPER(COMPANY_NAME) like '%' || UPPER(:search) || '%'
                    )
                order by 
                    T_NEWS_FEED_ID desc
            )
            where rownum < " + KGlobal.DEFAULT_FEED_COUNT;

            DataTable dt = dbl.GetDataTable(sxQuery, prm, prm2);
            
            return dt; 
        }

        public NewsFeedData[] GetUserOlderFeedFilteredList(String sxUserId, int iOldestId, String sxFilter)
        {
            DataTable dt = GetUserOlderFeedFilteredTable(sxUserId, iOldestId, sxFilter);
            return ConvertTableToObjectArray(dt);
        }
        private DataTable GetUserOlderFeedFilteredTable(String sxUserId, int iOldestId, String sxFilter)
        {
            DBLib dbl = new DBLib();

            OracleParameter prm = new OracleParameter();
            prm.ParameterName = "search";
            prm.OracleDbType = OracleDbType.VarChar;
            prm.Value = sxFilter;

            OracleParameter prm2 = new OracleParameter();
            prm2.ParameterName = "prmID";
            prm2.OracleDbType = OracleDbType.Number;
            prm2.Value = iOldestId;

            OracleParameter prm3 = new OracleParameter();
            prm3.ParameterName = "prmUserID";
            prm3.OracleDbType = OracleDbType.VarChar;
            prm3.Value = sxUserId;

            String sxQuery =
            @"
            select * from
            (
                select
                    T_NEWS_FEED_ID,
                    CONTENT,
                    TITLE,
                    POSTING_USER_ID,
                    POSTING_USER_NAME,
                    POSTED_USER_ID,
                    POSTED_USER_NAME,
                    CUSTOMER_ID,    
                    COMPANY_NAME,
                    SOURCE_NAME,
                    SOURCE_URL,
                    POST_DATE,     
                    NEWS_FEED_DATA,
                    P_NEWS_FEED_DATA_TYPE_ID,
                    NEWS_FEED_DATA_TYPE,
                    COMMENT_COUNT 
                from 
                    V_ICP_NEWS_FEED
                where 
                    ( 
                        POSTING_USER_ID = :prmUserID
                        or POSTED_USER_ID = :prmUserID
                    )
                    and T_NEWS_FEED_ID < :prmID
                    and
                    (
                        UPPER(CONTENT) like '%' || UPPER(:search) || '%'
                        or UPPER(TITLE) like '%' || UPPER(:search) || '%'
                        or UPPER(POSTING_USER_NAME) like '%' || UPPER(:search) || '%'
                        or UPPER(POSTED_USER_NAME) like '%' || UPPER(:search) || '%'
                        or UPPER(COMPANY_NAME) like '%' || UPPER(:search) || '%'
                    )
                order by 
                    T_NEWS_FEED_ID desc
            )
            where rownum < " + KGlobal.DEFAULT_FEED_COUNT;

            DataTable dt = dbl.GetDataTable(sxQuery, prm, prm2, prm3);
            
            return dt;
        }

        public NewsFeedData[] GetNewsFeedList()
        {
            DataTable dt = GetNewsFeedTable();
            return ConvertTableToObjectArray(dt);
        }
        private DataTable GetNewsFeedTable()
        {
            DBLib dbl = new DBLib();

            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count-1].ParameterName = "prmRoleID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ICPUser.RoleId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmUID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

//            String sxQuery = 
//            @"
//            select * from 
//            (
//                select
//                    v1.T_NEWS_FEED_ID,
//                    v1.CONTENT,
//                    v1.TITLE,
//                    v1.POSTING_USER_ID,
//                    v1.POSTING_USER_NAME,
//                    v1.POSTED_USER_ID,
//                    v1.POSTED_USER_NAME,
//                    v1.CUSTOMER_ID,    
//                    v1.COMPANY_NAME,
//                    v1.SOURCE_NAME,
//                    v1.SOURCE_URL,
//                    v1.POST_DATE,     
//                    v1.NEWS_FEED_DATA,
//                    v1.P_NEWS_FEED_DATA_TYPE_ID,
//                    v1.NEWS_FEED_DATA_TYPE,
//                    v1.COMMENT_COUNT 
//                from
//                    V_ICP_NEWS_FEED v1
//                    left join T_FEED_SUBSCRIPTION t1
//                        on v1.FEED_SOURCE_ID = t1.FEED_SOURCE_ID
//                    left join T_FEED_UNSUBSCRIBE t2
//                        on v1.FEED_SOURCE_ID = t2.FEED_SOURCE_ID
//                        and t2.ICP_USER_ID = :prmUID
//                    left join T_BLOCK_USER t3
//                        on v1.POSTING_USER_ID = t3.BLOCKED_USER_ID
//                        and t3.ICP_USER_ID = :prmUID
//                    left join T_BLOCK_CUSTOMER t4
//                        on v1.CUSTOMER_ID = t4.CUSTOMER_ID
//                        and t4.ICP_USER_ID = :prmUID
//                where
//                    t1.P_ICP_USER_ROLE_ID = :prmRoleID
//                    and t2.T_FEED_UNSUBSCRIBE_ID is NULL
//                    and (t3.T_BLOCK_USER_ID is NULL OR v1.CUSTOMER_ID is not null)
//                    and t4.T_BLOCK_CUSTOMER_ID is NULL
//                    {0}
//                order by 
//                    v1.T_NEWS_FEED_ID desc
//            )
//            where rownum < " + KGlobal.DEFAULT_FEED_COUNT;

            String sxExtraParam = String.Empty;
            if (ICPUser.RoleId == (int)ICPUserRole.CAR || ICPUser.RoleId == (int)ICPUserRole.CARAdminForum)
            {
                sxExtraParam = sxFilterHierarki_CAR3;
            }
            else if (ICPUser.RoleId == (int)ICPUserRole.Sales)
            {
                sxExtraParam = sxFilterHierarki_Sales3;
            }
            else
            {
                sxExtraParam = String.Empty;
            }
            String sxQuery = String.Format(
                @"
                SELECT   
                    t1.T_NEWS_FEED_ID,
                    t1.CONTENT,
                    t1.TITLE,
                    t1.POSTING_USER_ID,
                    v1.EMPLOYEE_NAME POSTING_USER_NAME,
                    t1.POSTED_USER_ID,
                    v2.EMPLOYEE_NAME POSTED_USER_NAME,
                    t1.CUSTOMER_ID,
                    t1.COMPANY_NAME,
                    m1.FEED_SOURCE_ID,
                    m1.NAME SOURCE_NAME,
                    m1.URL SOURCE_URL,
                    t1.POST_DATE,
                    COUNT (t3.T_FEED_COMMENT_ID) COMMENT_COUNT
               FROM 
                    (
                        select * from
                        (
                            select NF2.* from
                            (
                                select * from                        
                                (
                                    select t0.*, v3.SALES_EMPLOYEE_NO, v3.CAR_EMPLOYEE_NO, v3.COMPANY_NAME
                                    from 
                                        T_NEWS_FEED t0
                                        LEFT JOIN V_ICP_COMPANY_CAR_SALES v3
                                            ON t0.CUSTOMER_ID = v3.CUSTOMER_ID
                                    WHERE   NVL (t0.IS_DELETED, 'N') = 'N'                                    
                                    -- ditambah and buat OLDER dan UPDATE        
                                ) NF
                                -- jadi parameter hierarki : START
                                {1}
                                -- jadi parameter hierarki : END
                                order by NF.T_NEWS_FEED_ID desc
                            ) NF2
                            left join T_FEED_SUBSCRIPTION x1
                                on NF2.FEED_SOURCE_ID = x1.FEED_SOURCE_ID
                            left join T_FEED_UNSUBSCRIBE x2
                                on NF2.FEED_SOURCE_ID = x2.FEED_SOURCE_ID
                                and x2.ICP_USER_ID = :prmUID
                            left join T_BLOCK_USER x3
                                on NF2.POSTING_USER_ID = x3.BLOCKED_USER_ID
                                and x3.ICP_USER_ID = :prmUID
                            left join T_BLOCK_CUSTOMER x4
                                on NF2.CUSTOMER_ID = x4.CUSTOMER_ID
                                and x4.ICP_USER_ID = :prmUID
                            where
                                x1.P_ICP_USER_ROLE_ID = :prmRoleID
                                and x2.T_FEED_UNSUBSCRIBE_ID is NULL
                                and (x3.T_BLOCK_USER_ID is NULL OR NF2.CUSTOMER_ID is not null)
                                and x4.T_BLOCK_CUSTOMER_ID is NULL
                        )   
                        where rownum <= {0} -- ini pake string format, masukin pagesize
                    ) t1
                    LEFT JOIN
                    T_FEED_COMMENT t3
                    ON t1.T_NEWS_FEED_ID = t3.T_NEWS_FEED_ID
                    LEFT JOIN
                    FEED_SOURCE m1
                    ON t1.FEED_SOURCE_ID = m1.FEED_SOURCE_ID
                    LEFT JOIN
                    V_ICP_USER v1
                    ON t1.POSTING_USER_ID = v1.ICP_USER_ID
                    LEFT JOIN
                    V_ICP_USER v2
                    ON t1.POSTED_USER_ID = v2.ICP_USER_ID                                    
            GROUP BY   t1.T_NEWS_FEED_ID,
                      t1.CONTENT,
                      t1.TITLE,
                      t1.POSTING_USER_ID,
                      v1.EMPLOYEE_NAME,
                      t1.POSTED_USER_ID,
                      v2.EMPLOYEE_NAME,
                      t1.CUSTOMER_ID,
                      t1.COMPANY_NAME,
                      m1.FEED_SOURCE_ID,
                      m1.NAME,
                      m1.URL,
                      t1.POST_DATE
            ORDER BY   t1.T_NEWS_FEED_ID DESC",
            KGlobal.DEFAULT_FEED_COUNT,
            sxExtraParam);

            //if (ICPUser.RoleId == (int)ICPUserRole.CAR || ICPUser.RoleId == (int)ICPUserRole.CARAdminForum)
            //{
            //    sxQuery = String.Format(sxQuery, sxFilterHierarki_CAR);
            //}
            //else if (ICPUser.RoleId == (int)ICPUserRole.Sales)
            //{
            //    sxQuery = String.Format(sxQuery, sxFilterHierarki_Sales);
            //}
            //else
            //{
            //    sxQuery = String.Format(sxQuery, String.Empty);
            //}

            DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
            
            return dt;
        }

        public NewsFeedData[] GetNewsFeedUpdateList(int iNewestId)
        {
            DataTable dt = GetNewsFeedUpdateTable(iNewestId);
            return ConvertTableToObjectArray(dt);
        }
        private DataTable GetNewsFeedUpdateTable(int iNewestId)
        {
            DBLib dbl = new DBLib();

            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = iNewestId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmRoleID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ICPUser.RoleId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmUID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

//            String sxQuery =
//            @"
//            select
//                v1.T_NEWS_FEED_ID,
//                v1.CONTENT,
//                v1.TITLE,
//                v1.POSTING_USER_ID,
//                v1.POSTING_USER_NAME,
//                v1.POSTED_USER_ID,
//                v1.POSTED_USER_NAME,
//                v1.CUSTOMER_ID,    
//                v1.COMPANY_NAME,
//                v1.SOURCE_NAME,
//                v1.SOURCE_URL,
//                v1.POST_DATE,     
//                v1.NEWS_FEED_DATA,
//                v1.P_NEWS_FEED_DATA_TYPE_ID,
//                v1.NEWS_FEED_DATA_TYPE,
//                v1.COMMENT_COUNT 
//            from
//                V_ICP_NEWS_FEED v1
//                left join T_FEED_SUBSCRIPTION t1
//                    on v1.FEED_SOURCE_ID = t1.FEED_SOURCE_ID
//                left join T_FEED_UNSUBSCRIBE t2
//                    on v1.FEED_SOURCE_ID = t2.FEED_SOURCE_ID
//                    and t2.ICP_USER_ID = :prmUID
//                left join T_BLOCK_USER t3
//                    on v1.POSTING_USER_ID = t3.BLOCKED_USER_ID
//                    and t3.ICP_USER_ID = :prmUID
//                left join T_BLOCK_CUSTOMER t4
//                    on v1.CUSTOMER_ID = t4.CUSTOMER_ID
//                    and t4.ICP_USER_ID = :prmUID
//            where
//                v1.T_NEWS_FEED_ID > :prmID
//                and t1.P_ICP_USER_ROLE_ID = :prmRoleID
//                and t2.T_FEED_UNSUBSCRIBE_ID is NULL
//                and (t3.T_BLOCK_USER_ID is NULL OR v1.CUSTOMER_ID is not null)
//                and t4.T_BLOCK_CUSTOMER_ID is NULL
//                {0}
//            order by 
//                v1.T_NEWS_FEED_ID desc";

//            if (ICPUser.RoleId == (int)ICPUserRole.CAR || ICPUser.RoleId == (int)ICPUserRole.CARAdminForum)
//            {
//                sxQuery = String.Format(sxQuery, sxFilterHierarki_CAR);
//            }
//            else if (ICPUser.RoleId == (int)ICPUserRole.Sales)
//            {
//                sxQuery = String.Format(sxQuery, sxFilterHierarki_Sales);
//            }
//            else
//            {
//                sxQuery = String.Format(sxQuery, String.Empty);
//            }

            String sxExtraParam = String.Empty;
            if (ICPUser.RoleId == (int)ICPUserRole.CAR || ICPUser.RoleId == (int)ICPUserRole.CARAdminForum)
            {
                sxExtraParam = sxFilterHierarki_CAR3;
            }
            else if (ICPUser.RoleId == (int)ICPUserRole.Sales)
            {
                sxExtraParam = sxFilterHierarki_Sales3;
            }
            else
            {
                sxExtraParam = String.Empty;
            }
            String sxQuery = String.Format(
                @"
                SELECT   
                    t1.T_NEWS_FEED_ID,
                    t1.CONTENT,
                    t1.TITLE,
                    t1.POSTING_USER_ID,
                    v1.EMPLOYEE_NAME POSTING_USER_NAME,
                    t1.POSTED_USER_ID,
                    v2.EMPLOYEE_NAME POSTED_USER_NAME,
                    t1.CUSTOMER_ID,
                    t1.COMPANY_NAME,
                    m1.FEED_SOURCE_ID,
                    m1.NAME SOURCE_NAME,
                    m1.URL SOURCE_URL,
                    t1.POST_DATE,
                    COUNT (t3.T_FEED_COMMENT_ID) COMMENT_COUNT
               FROM 
                    (
                        select * from
                        (
                            select NF2.* from
                            (
                                select * from                        
                                (
                                    select t0.*, v3.SALES_EMPLOYEE_NO, v3.CAR_EMPLOYEE_NO, v3.COMPANY_NAME
                                    from 
                                        T_NEWS_FEED t0
                                        LEFT JOIN V_ICP_COMPANY_CAR_SALES v3
                                            ON t0.CUSTOMER_ID = v3.CUSTOMER_ID
                                    WHERE   NVL (t0.IS_DELETED, 'N') = 'N'                                    
                                    -- ditambah and buat OLDER dan UPDATE
                                    and
                                        t0.T_NEWS_FEED_ID > :prmID        
                                ) NF
                                -- jadi parameter hierarki : START
                                {0}
                                -- jadi parameter hierarki : END
                                order by NF.T_NEWS_FEED_ID desc
                            ) NF2
                            left join T_FEED_SUBSCRIPTION x1
                                on NF2.FEED_SOURCE_ID = x1.FEED_SOURCE_ID
                            left join T_FEED_UNSUBSCRIBE x2
                                on NF2.FEED_SOURCE_ID = x2.FEED_SOURCE_ID
                                and x2.ICP_USER_ID = :prmUID
                            left join T_BLOCK_USER x3
                                on NF2.POSTING_USER_ID = x3.BLOCKED_USER_ID
                                and x3.ICP_USER_ID = :prmUID
                            left join T_BLOCK_CUSTOMER x4
                                on NF2.CUSTOMER_ID = x4.CUSTOMER_ID
                                and x4.ICP_USER_ID = :prmUID
                            where
                                x1.P_ICP_USER_ROLE_ID = :prmRoleID
                                and x2.T_FEED_UNSUBSCRIBE_ID is NULL
                                and (x3.T_BLOCK_USER_ID is NULL OR NF2.CUSTOMER_ID is not null)
                                and x4.T_BLOCK_CUSTOMER_ID is NULL
                        )   
                    ) t1
                    LEFT JOIN
                    T_FEED_COMMENT t3
                    ON t1.T_NEWS_FEED_ID = t3.T_NEWS_FEED_ID
                    LEFT JOIN
                    FEED_SOURCE m1
                    ON t1.FEED_SOURCE_ID = m1.FEED_SOURCE_ID
                    LEFT JOIN
                    V_ICP_USER v1
                    ON t1.POSTING_USER_ID = v1.ICP_USER_ID
                    LEFT JOIN
                    V_ICP_USER v2
                    ON t1.POSTED_USER_ID = v2.ICP_USER_ID                                    
            GROUP BY   t1.T_NEWS_FEED_ID,
                      t1.CONTENT,
                      t1.TITLE,
                      t1.POSTING_USER_ID,
                      v1.EMPLOYEE_NAME,
                      t1.POSTED_USER_ID,
                      v2.EMPLOYEE_NAME,
                      t1.CUSTOMER_ID,
                      t1.COMPANY_NAME,
                      m1.FEED_SOURCE_ID,
                      m1.NAME,
                      m1.URL,
                      t1.POST_DATE
            ORDER BY   t1.T_NEWS_FEED_ID DESC",
            sxExtraParam);

            DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
            
            return dt;
        }

        public NewsFeedData[] GetNewsFeedOlderList(int iOldestId)
        {
            DataTable dt = GetNewsFeedOlderTable(iOldestId);
            return ConvertTableToObjectArray(dt);
        }
        private DataTable GetNewsFeedOlderTable(int iOldestId)
        {
            DBLib dbl = new DBLib();

            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = iOldestId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmRoleID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ICPUser.RoleId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmUID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

//            String sxQuery =
//            @"
//            select * from
//            (
//                select
//                    v1.T_NEWS_FEED_ID,
//                    v1.CONTENT,
//                    v1.TITLE,
//                    v1.POSTING_USER_ID,
//                    v1.POSTING_USER_NAME,
//                    v1.POSTED_USER_ID,
//                    v1.POSTED_USER_NAME,
//                    v1.CUSTOMER_ID,    
//                    v1.COMPANY_NAME,
//                    v1.SOURCE_NAME,
//                    v1.SOURCE_URL,
//                    v1.POST_DATE,     
//                    v1.NEWS_FEED_DATA,
//                    v1.P_NEWS_FEED_DATA_TYPE_ID,
//                    v1.NEWS_FEED_DATA_TYPE,
//                    v1.COMMENT_COUNT 
//                from
//                    V_ICP_NEWS_FEED v1
//                    left join T_FEED_SUBSCRIPTION t1
//                        on v1.FEED_SOURCE_ID = t1.FEED_SOURCE_ID
//                    left join T_FEED_UNSUBSCRIBE t2
//                        on v1.FEED_SOURCE_ID = t2.FEED_SOURCE_ID
//                        and t2.ICP_USER_ID = :prmUID
//                    left join T_BLOCK_USER t3
//                        on v1.POSTING_USER_ID = t3.BLOCKED_USER_ID
//                        and t3.ICP_USER_ID = :prmUID
//                    left join T_BLOCK_CUSTOMER t4
//                        on v1.CUSTOMER_ID = t4.CUSTOMER_ID
//                        and t4.ICP_USER_ID = :prmUID
//                where
//                    v1.T_NEWS_FEED_ID < :prmID
//                    and t1.P_ICP_USER_ROLE_ID = :prmRoleID
//                    and t2.T_FEED_UNSUBSCRIBE_ID is NULL
//                    and (t3.T_BLOCK_USER_ID is NULL OR v1.CUSTOMER_ID is not null)
//                    and t4.T_BLOCK_CUSTOMER_ID is NULL
//                    {0}
//                order by 
//                    v1.T_NEWS_FEED_ID desc
//            )
//            where rownum < " + KGlobal.DEFAULT_FEED_COUNT;

//            if (ICPUser.RoleId == (int)ICPUserRole.CAR || ICPUser.RoleId == (int)ICPUserRole.CARAdminForum)
//            {
//                sxQuery = String.Format(sxQuery, sxFilterHierarki_CAR);
//            }
//            else if (ICPUser.RoleId == (int)ICPUserRole.Sales)
//            {
//                sxQuery = String.Format(sxQuery, sxFilterHierarki_Sales);
//            }
//            else
//            {
//                sxQuery = String.Format(sxQuery, String.Empty);
//            }

            String sxExtraParam = String.Empty;
            if (ICPUser.RoleId == (int)ICPUserRole.CAR || ICPUser.RoleId == (int)ICPUserRole.CARAdminForum)
            {
                sxExtraParam = sxFilterHierarki_CAR3;
            }
            else if (ICPUser.RoleId == (int)ICPUserRole.Sales)
            {
                sxExtraParam = sxFilterHierarki_Sales3;
            }
            else
            {
                sxExtraParam = String.Empty;
            }
            String sxQuery = String.Format(
                @"
                SELECT   
                    t1.T_NEWS_FEED_ID,
                    t1.CONTENT,
                    t1.TITLE,
                    t1.POSTING_USER_ID,
                    v1.EMPLOYEE_NAME POSTING_USER_NAME,
                    t1.POSTED_USER_ID,
                    v2.EMPLOYEE_NAME POSTED_USER_NAME,
                    t1.CUSTOMER_ID,
                    t1.COMPANY_NAME,
                    m1.FEED_SOURCE_ID,
                    m1.NAME SOURCE_NAME,
                    m1.URL SOURCE_URL,
                    t1.POST_DATE,
                    COUNT (t3.T_FEED_COMMENT_ID) COMMENT_COUNT
               FROM 
                    (
                        select * from
                        (
                            select NF2.* from
                            (
                                select * from                        
                                (
                                    select t0.*, v3.SALES_EMPLOYEE_NO, v3.CAR_EMPLOYEE_NO, v3.COMPANY_NAME
                                    from 
                                        T_NEWS_FEED t0
                                        LEFT JOIN V_ICP_COMPANY_CAR_SALES v3
                                            ON t0.CUSTOMER_ID = v3.CUSTOMER_ID
                                    WHERE   NVL (t0.IS_DELETED, 'N') = 'N'                                    
                                    -- ditambah and buat OLDER dan UPDATE
                                    and
                                        t0.T_NEWS_FEED_ID < :prmID        
                                ) NF
                                -- jadi parameter hierarki : START
                                {1}
                                -- jadi parameter hierarki : END
                                order by NF.T_NEWS_FEED_ID desc
                            ) NF2
                            left join T_FEED_SUBSCRIPTION x1
                                on NF2.FEED_SOURCE_ID = x1.FEED_SOURCE_ID
                            left join T_FEED_UNSUBSCRIBE x2
                                on NF2.FEED_SOURCE_ID = x2.FEED_SOURCE_ID
                                and x2.ICP_USER_ID = :prmUID
                            left join T_BLOCK_USER x3
                                on NF2.POSTING_USER_ID = x3.BLOCKED_USER_ID
                                and x3.ICP_USER_ID = :prmUID
                            left join T_BLOCK_CUSTOMER x4
                                on NF2.CUSTOMER_ID = x4.CUSTOMER_ID
                                and x4.ICP_USER_ID = :prmUID
                            where
                                x1.P_ICP_USER_ROLE_ID = :prmRoleID
                                and x2.T_FEED_UNSUBSCRIBE_ID is NULL
                                and (x3.T_BLOCK_USER_ID is NULL OR NF2.CUSTOMER_ID is not null)
                                and x4.T_BLOCK_CUSTOMER_ID is NULL
                        )   
                        where rownum <= {0} -- ini pake string format, masukin pagesize
                    ) t1
                    LEFT JOIN
                    T_FEED_COMMENT t3
                    ON t1.T_NEWS_FEED_ID = t3.T_NEWS_FEED_ID
                    LEFT JOIN
                    FEED_SOURCE m1
                    ON t1.FEED_SOURCE_ID = m1.FEED_SOURCE_ID
                    LEFT JOIN
                    V_ICP_USER v1
                    ON t1.POSTING_USER_ID = v1.ICP_USER_ID
                    LEFT JOIN
                    V_ICP_USER v2
                    ON t1.POSTED_USER_ID = v2.ICP_USER_ID                                    
            GROUP BY   t1.T_NEWS_FEED_ID,
                      t1.CONTENT,
                      t1.TITLE,
                      t1.POSTING_USER_ID,
                      v1.EMPLOYEE_NAME,
                      t1.POSTED_USER_ID,
                      v2.EMPLOYEE_NAME,
                      t1.CUSTOMER_ID,
                      t1.COMPANY_NAME,
                      m1.FEED_SOURCE_ID,
                      m1.NAME,
                      m1.URL,
                      t1.POST_DATE
            ORDER BY   t1.T_NEWS_FEED_ID DESC",
            KGlobal.DEFAULT_FEED_COUNT,
            sxExtraParam);

            DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
            
            return dt;
        }

        public NewsFeedData[] GetNewsFeedFilteredList(String sxFilter)
        {
            DataTable dt = GetNewsFeedFilteredTable(sxFilter);
            return ConvertTableToObjectArray(dt);
        }
        private DataTable GetNewsFeedFilteredTable(String sxFilter)
        {
            DBLib dbl = new DBLib();

            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "search";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = sxFilter;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmRoleID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ICPUser.RoleId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmUID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

//            String sxQuery =
//            @"
//            select * from
//            (
//                select
//                    v1.T_NEWS_FEED_ID,
//                    v1.CONTENT,
//                    v1.TITLE,
//                    v1.POSTING_USER_ID,
//                    v1.POSTING_USER_NAME,
//                    v1.POSTED_USER_ID,
//                    v1.POSTED_USER_NAME,
//                    v1.CUSTOMER_ID,    
//                    v1.COMPANY_NAME,
//                    v1.SOURCE_NAME,
//                    v1.SOURCE_URL,
//                    v1.POST_DATE,     
//                    v1.NEWS_FEED_DATA,
//                    v1.P_NEWS_FEED_DATA_TYPE_ID,
//                    v1.NEWS_FEED_DATA_TYPE,
//                    v1.COMMENT_COUNT 
//                from 
//                    V_ICP_NEWS_FEED v1
//                    left join T_FEED_SUBSCRIPTION t1
//                        on v1.FEED_SOURCE_ID = t1.FEED_SOURCE_ID
//                    left join T_FEED_UNSUBSCRIBE t2
//                        on v1.FEED_SOURCE_ID = t2.FEED_SOURCE_ID
//                        and t2.ICP_USER_ID = :prmUID
//                    left join T_BLOCK_USER t3
//                        on v1.POSTING_USER_ID = t3.BLOCKED_USER_ID
//                        and t3.ICP_USER_ID = :prmUID
//                    left join T_BLOCK_CUSTOMER t4
//                        on v1.CUSTOMER_ID = t4.CUSTOMER_ID
//                        and t4.ICP_USER_ID = :prmUID
//                where 
//                    t1.P_ICP_USER_ROLE_ID = :prmRoleID
//                    and t2.T_FEED_UNSUBSCRIBE_ID is NULL
//                    and (t3.T_BLOCK_USER_ID is NULL OR v1.CUSTOMER_ID is not null)
//                    and t4.T_BLOCK_CUSTOMER_ID is NULL
//                    and
//                    (
//                        UPPER(v1.CONTENT) like '%' || UPPER(:search) || '%'
//                        or UPPER(v1.TITLE) like '%' || UPPER(:search) || '%'
//                        or UPPER(v1.POSTING_USER_NAME) like '%' || UPPER(:search) || '%'
//                        or UPPER(v1.POSTED_USER_NAME) like '%' || UPPER(:search) || '%'
//                        or UPPER(v1.COMPANY_NAME) like '%' || UPPER(:search) || '%'
//                    )
//                    {0}
//                order by 
//                    v1.T_NEWS_FEED_ID desc
//            )
//            where rownum < " + KGlobal.DEFAULT_FEED_COUNT;

//            if (ICPUser.RoleId == (int)ICPUserRole.CAR || ICPUser.RoleId == (int)ICPUserRole.CARAdminForum)
//            {
//                sxQuery = String.Format(sxQuery, sxFilterHierarki_CAR);
//            }
//            else if (ICPUser.RoleId == (int)ICPUserRole.Sales)
//            {
//                sxQuery = String.Format(sxQuery, sxFilterHierarki_Sales);
//            }
//            else
//            {
//                sxQuery = String.Format(sxQuery, String.Empty);
//            }
            
            String sxExtraParam = String.Empty;
            if (ICPUser.RoleId == (int)ICPUserRole.CAR || ICPUser.RoleId == (int)ICPUserRole.CARAdminForum)
            {
                sxExtraParam = sxFilterHierarki_CAR3;
            }
            else if (ICPUser.RoleId == (int)ICPUserRole.Sales)
            {
                sxExtraParam = sxFilterHierarki_Sales3;
            }
            else
            {
                sxExtraParam = String.Empty;
            }
            String sxQuery = String.Format(
                @"
                SELECT   
                    t1.T_NEWS_FEED_ID,
                    t1.CONTENT,
                    t1.TITLE,
                    t1.POSTING_USER_ID,
                    v1.EMPLOYEE_NAME POSTING_USER_NAME,
                    t1.POSTED_USER_ID,
                    v2.EMPLOYEE_NAME POSTED_USER_NAME,
                    t1.CUSTOMER_ID,
                    t1.COMPANY_NAME,
                    m1.FEED_SOURCE_ID,
                    m1.NAME SOURCE_NAME,
                    m1.URL SOURCE_URL,
                    t1.POST_DATE,
                    COUNT (t3.T_FEED_COMMENT_ID) COMMENT_COUNT
               FROM 
                    (
                        select * from
                        (
                            select NF2.* from
                            (
                                select * from                        
                                (
                                    select t0.*, v3.SALES_EMPLOYEE_NO, v3.CAR_EMPLOYEE_NO, v3.COMPANY_NAME
                                    from 
                                        T_NEWS_FEED t0
                                        LEFT JOIN V_ICP_COMPANY_CAR_SALES v3
                                            ON t0.CUSTOMER_ID = v3.CUSTOMER_ID
                                    WHERE   NVL (t0.IS_DELETED, 'N') = 'N'                                    
                                    -- ditambah and buat OLDER dan UPDATE
                                    and
                                    (
                                        UPPER(t0.CONTENT) like '%' || UPPER(:search) || '%'
                                        or UPPER(t0.TITLE) like '%' || UPPER(:search) || '%'
                                        or UPPER(t0.POSTING_USER_NAME) like '%' || UPPER(:search) || '%'
                                        or UPPER(t0.POSTED_USER_NAME) like '%' || UPPER(:search) || '%'
                                        or UPPER(t0.COMPANY_NAME) like '%' || UPPER(:search) || '%'
                                    )        
                                ) NF
                                -- jadi parameter hierarki : START
                                {1}
                                -- jadi parameter hierarki : END
                                order by NF.T_NEWS_FEED_ID desc
                            ) NF2
                            left join T_FEED_SUBSCRIPTION x1
                                on NF2.FEED_SOURCE_ID = x1.FEED_SOURCE_ID
                            left join T_FEED_UNSUBSCRIBE x2
                                on NF2.FEED_SOURCE_ID = x2.FEED_SOURCE_ID
                                and x2.ICP_USER_ID = :prmUID
                            left join T_BLOCK_USER x3
                                on NF2.POSTING_USER_ID = x3.BLOCKED_USER_ID
                                and x3.ICP_USER_ID = :prmUID
                            left join T_BLOCK_CUSTOMER x4
                                on NF2.CUSTOMER_ID = x4.CUSTOMER_ID
                                and x4.ICP_USER_ID = :prmUID
                            where
                                x1.P_ICP_USER_ROLE_ID = :prmRoleID
                                and x2.T_FEED_UNSUBSCRIBE_ID is NULL
                                and (x3.T_BLOCK_USER_ID is NULL OR NF2.CUSTOMER_ID is not null)
                                and x4.T_BLOCK_CUSTOMER_ID is NULL
                        )   
                        where rownum <= {0} -- ini pake string format, masukin pagesize
                    ) t1
                    LEFT JOIN
                    T_FEED_COMMENT t3
                    ON t1.T_NEWS_FEED_ID = t3.T_NEWS_FEED_ID
                    LEFT JOIN
                    FEED_SOURCE m1
                    ON t1.FEED_SOURCE_ID = m1.FEED_SOURCE_ID
                    LEFT JOIN
                    V_ICP_USER v1
                    ON t1.POSTING_USER_ID = v1.ICP_USER_ID
                    LEFT JOIN
                    V_ICP_USER v2
                    ON t1.POSTED_USER_ID = v2.ICP_USER_ID                                    
            GROUP BY   t1.T_NEWS_FEED_ID,
                      t1.CONTENT,
                      t1.TITLE,
                      t1.POSTING_USER_ID,
                      v1.EMPLOYEE_NAME,
                      t1.POSTED_USER_ID,
                      v2.EMPLOYEE_NAME,
                      t1.CUSTOMER_ID,
                      t1.COMPANY_NAME,
                      m1.FEED_SOURCE_ID,
                      m1.NAME,
                      m1.URL,
                      t1.POST_DATE
            ORDER BY   t1.T_NEWS_FEED_ID DESC",
            KGlobal.DEFAULT_FEED_COUNT,
            sxExtraParam);

            DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
            
            return dt;
        }

        public NewsFeedData[] GetOlderFeedFilteredList(int iOldestId, String sxFilter)
        {
            DataTable dt = GetOlderFeedFilteredTable(iOldestId, sxFilter);
            return ConvertTableToObjectArray(dt);
        }
        private DataTable GetOlderFeedFilteredTable(int iOldestId, String sxFilter)
        {
            DBLib dbl = new DBLib();

            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "search";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = sxFilter;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = iOldestId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmRoleID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ICPUser.RoleId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmUID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

//            String sxQuery =
//            @"
//            select * from
//            (
//                select
//                    v1.T_NEWS_FEED_ID,
//                    v1.CONTENT,
//                    v1.TITLE,
//                    v1.POSTING_USER_ID,
//                    v1.POSTING_USER_NAME,
//                    v1.POSTED_USER_ID,
//                    v1.POSTED_USER_NAME,
//                    v1.CUSTOMER_ID,    
//                    v1.COMPANY_NAME,
//                    v1.SOURCE_NAME,
//                    v1.SOURCE_URL,
//                    v1.POST_DATE,     
//                    v1.NEWS_FEED_DATA,
//                    v1.P_NEWS_FEED_DATA_TYPE_ID,
//                    v1.NEWS_FEED_DATA_TYPE,
//                    v1.COMMENT_COUNT 
//                from 
//                    V_ICP_NEWS_FEED v1
//                    left join T_FEED_SUBSCRIPTION t1
//                        on v1.FEED_SOURCE_ID = t1.FEED_SOURCE_ID
//                    left join T_FEED_UNSUBSCRIBE t2
//                        on v1.FEED_SOURCE_ID = t2.FEED_SOURCE_ID
//                        and t2.ICP_USER_ID = :prmUID
//                    left join T_BLOCK_USER t3
//                        on v1.POSTING_USER_ID = t3.BLOCKED_USER_ID
//                        and t3.ICP_USER_ID = :prmUID
//                    left join T_BLOCK_CUSTOMER t4
//                        on v1.CUSTOMER_ID = t4.CUSTOMER_ID
//                        and t4.ICP_USER_ID = :prmUID
//                where 
//                    v1.T_NEWS_FEED_ID < :prmID
//                    and t1.P_ICP_USER_ROLE_ID = :prmRoleID
//                    and t2.T_FEED_UNSUBSCRIBE_ID is NULL
//                    and (t3.T_BLOCK_USER_ID is NULL OR v1.CUSTOMER_ID is not null)
//                    and t4.T_BLOCK_CUSTOMER_ID is NULL
//                    and
//                    (
//                        UPPER(v1.CONTENT) like '%' || UPPER(:search) || '%'
//                        or UPPER(v1.TITLE) like '%' || UPPER(:search) || '%'
//                        or UPPER(v1.POSTING_USER_NAME) like '%' || UPPER(:search) || '%'
//                        or UPPER(v1.POSTED_USER_NAME) like '%' || UPPER(:search) || '%'
//                        or UPPER(v1.COMPANY_NAME) like '%' || UPPER(:search) || '%'
//                    )
//                    {0}
//                order by 
//                    v1.T_NEWS_FEED_ID desc
//            )
//            where rownum < " + KGlobal.DEFAULT_FEED_COUNT;

//            if (ICPUser.RoleId == (int)ICPUserRole.CAR || ICPUser.RoleId == (int)ICPUserRole.CARAdminForum)
//            {
//                sxQuery = String.Format(sxQuery, sxFilterHierarki_CAR);
//            }
//            else if (ICPUser.RoleId == (int)ICPUserRole.Sales)
//            {
//                sxQuery = String.Format(sxQuery, sxFilterHierarki_Sales);
//            }
//            else
//            {
//                sxQuery = String.Format(sxQuery, String.Empty);
//            }

            String sxExtraParam = String.Empty;
            if (ICPUser.RoleId == (int)ICPUserRole.CAR || ICPUser.RoleId == (int)ICPUserRole.CARAdminForum)
            {
                sxExtraParam = sxFilterHierarki_CAR3;
            }
            else if (ICPUser.RoleId == (int)ICPUserRole.Sales)
            {
                sxExtraParam = sxFilterHierarki_Sales3;
            }
            else
            {
                sxExtraParam = String.Empty;
            }
            String sxQuery = String.Format(
                @"
                SELECT   
                    t1.T_NEWS_FEED_ID,
                    t1.CONTENT,
                    t1.TITLE,
                    t1.POSTING_USER_ID,
                    v1.EMPLOYEE_NAME POSTING_USER_NAME,
                    t1.POSTED_USER_ID,
                    v2.EMPLOYEE_NAME POSTED_USER_NAME,
                    t1.CUSTOMER_ID,
                    t1.COMPANY_NAME,
                    m1.FEED_SOURCE_ID,
                    m1.NAME SOURCE_NAME,
                    m1.URL SOURCE_URL,
                    t1.POST_DATE,
                    COUNT (t3.T_FEED_COMMENT_ID) COMMENT_COUNT
               FROM 
                    (
                        select * from
                        (
                            select NF2.* from
                            (
                                select * from                        
                                (
                                    select t0.*, v3.SALES_EMPLOYEE_NO, v3.CAR_EMPLOYEE_NO, v3.COMPANY_NAME
                                    from 
                                        T_NEWS_FEED t0
                                        LEFT JOIN V_ICP_COMPANY_CAR_SALES v3
                                            ON t0.CUSTOMER_ID = v3.CUSTOMER_ID
                                    WHERE   NVL (t0.IS_DELETED, 'N') = 'N'                                    
                                    -- ditambah and buat OLDER dan UPDATE
                                    and
                                        t0.T_NEWS_FEED_ID < :prmID 
                                    and
                                    (
                                        UPPER(t0.CONTENT) like '%' || UPPER(:search) || '%'
                                        or UPPER(t0.TITLE) like '%' || UPPER(:search) || '%'
                                        or UPPER(t0.POSTING_USER_NAME) like '%' || UPPER(:search) || '%'
                                        or UPPER(t0.POSTED_USER_NAME) like '%' || UPPER(:search) || '%'
                                        or UPPER(t0.COMPANY_NAME) like '%' || UPPER(:search) || '%'
                                    )       
                                ) NF
                                -- jadi parameter hierarki : START
                                {1}
                                -- jadi parameter hierarki : END
                                order by NF.T_NEWS_FEED_ID desc
                            ) NF2
                            left join T_FEED_SUBSCRIPTION x1
                                on NF2.FEED_SOURCE_ID = x1.FEED_SOURCE_ID
                            left join T_FEED_UNSUBSCRIBE x2
                                on NF2.FEED_SOURCE_ID = x2.FEED_SOURCE_ID
                                and x2.ICP_USER_ID = :prmUID
                            left join T_BLOCK_USER x3
                                on NF2.POSTING_USER_ID = x3.BLOCKED_USER_ID
                                and x3.ICP_USER_ID = :prmUID
                            left join T_BLOCK_CUSTOMER x4
                                on NF2.CUSTOMER_ID = x4.CUSTOMER_ID
                                and x4.ICP_USER_ID = :prmUID
                            where
                                x1.P_ICP_USER_ROLE_ID = :prmRoleID
                                and x2.T_FEED_UNSUBSCRIBE_ID is NULL
                                and (x3.T_BLOCK_USER_ID is NULL OR NF2.CUSTOMER_ID is not null)
                                and x4.T_BLOCK_CUSTOMER_ID is NULL
                        )   
                        where rownum <= {0} -- ini pake string format, masukin pagesize
                    ) t1
                    LEFT JOIN
                    T_FEED_COMMENT t3
                    ON t1.T_NEWS_FEED_ID = t3.T_NEWS_FEED_ID
                    LEFT JOIN
                    FEED_SOURCE m1
                    ON t1.FEED_SOURCE_ID = m1.FEED_SOURCE_ID
                    LEFT JOIN
                    V_ICP_USER v1
                    ON t1.POSTING_USER_ID = v1.ICP_USER_ID
                    LEFT JOIN
                    V_ICP_USER v2
                    ON t1.POSTED_USER_ID = v2.ICP_USER_ID                                    
            GROUP BY   t1.T_NEWS_FEED_ID,
                      t1.CONTENT,
                      t1.TITLE,
                      t1.POSTING_USER_ID,
                      v1.EMPLOYEE_NAME,
                      t1.POSTED_USER_ID,
                      v2.EMPLOYEE_NAME,
                      t1.CUSTOMER_ID,
                      t1.COMPANY_NAME,
                      m1.FEED_SOURCE_ID,
                      m1.NAME,
                      m1.URL,
                      t1.POST_DATE
            ORDER BY   t1.T_NEWS_FEED_ID DESC",
            KGlobal.DEFAULT_FEED_COUNT,
            sxExtraParam);

            DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
            
            return dt;
        }
		
        public CommentData[] GetCommentList(int iNewsFeedId)
        {
            DataTable dt = GetNewsFeedCommentTable(iNewsFeedId);

            CommentData[] arrRet = new CommentData[dt.Rows.Count];

            for (int i = 0; i < arrRet.Length; i++)
            {
                arrRet[i] = new CommentData();

                arrRet[i].ID = dt.Rows[i].ReadInt("T_FEED_COMMENT_ID");
                arrRet[i].sxContent = dt.Rows[i].ReadString("VALUE");
                arrRet[i].sxUserId = dt.Rows[i].ReadString("ICP_USER_ID");
                arrRet[i].sxUserName = dt.Rows[i].ReadString("ICP_USER_NAME");
                arrRet[i].dtmDate = dt.Rows[i].ReadDateTime("COMMENT_DATE");
            }

            return arrRet;
        }
        private DataTable GetNewsFeedCommentTable(int iNewsFeedId)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iNewsFeedId;

            String sxQuery =
            @"
            select
                t1.T_FEED_COMMENT_ID,
                t1.VALUE,
                t1.ICP_USER_ID,
                v1.EMPLOYEE_NAME ICP_USER_NAME,
                t1.COMMENT_DATE
            from
                T_FEED_COMMENT t1
                left join V_ICP_USER v1
                    on t1.ICP_USER_ID = v1.ICP_USER_ID
            where
                t1.T_NEWS_FEED_ID = :prmID";

            DataTable dt = dbl.GetDataTable(sxQuery, prmID);
            
            return dt;
        }

        public int GetCommentCount(int iNewsFeedId)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iNewsFeedId;

            String sxQuery =
            @"
                select count(*)
                from
                    T_FEED_COMMENT 
                where
                    T_NEWS_FEED_ID = :prmID";

            int iReturn = Convert.ToInt32(dbl.GetSingleValue(sxQuery, prmID));
            
            return iReturn;
        }

        public NewsFeedData GetNewsFeed(int iNewsFeedId)
        {
            DBLib dbl = new DBLib();

            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = iNewsFeedId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmRoleID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ICPUser.RoleId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmUID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

//            String sxQuery =
//            @"
//            select
//                v1.T_NEWS_FEED_ID,
//                v1.CONTENT,
//                v1.TITLE,
//                v1.POSTING_USER_ID,
//                v1.POSTING_USER_NAME,
//                v1.POSTED_USER_ID,
//                v1.POSTED_USER_NAME,
//                v1.CUSTOMER_ID,    
//                v1.COMPANY_NAME,
//                v1.SOURCE_NAME,
//                v1.SOURCE_URL,
//                v1.POST_DATE,     
//                v1.NEWS_FEED_DATA,
//                v1.P_NEWS_FEED_DATA_TYPE_ID,
//                v1.NEWS_FEED_DATA_TYPE,
//                v1.COMMENT_COUNT 
//            from
//                V_ICP_NEWS_FEED v1
//                left join T_FEED_SUBSCRIPTION t1
//                    on v1.FEED_SOURCE_ID = t1.FEED_SOURCE_ID
//                left join T_FEED_UNSUBSCRIBE t2
//                    on v1.FEED_SOURCE_ID = t2.FEED_SOURCE_ID
//                    and t2.ICP_USER_ID = :prmUID
//                left join T_BLOCK_USER t3
//                    on v1.POSTING_USER_ID = t3.BLOCKED_USER_ID
//                    and t3.ICP_USER_ID = :prmUID
//                left join T_BLOCK_CUSTOMER t4
//                    on v1.CUSTOMER_ID = t4.CUSTOMER_ID
//                    and t4.ICP_USER_ID = :prmUID
//            where 
//                v1.T_NEWS_FEED_ID = :prmID
//                and t2.T_FEED_UNSUBSCRIBE_ID is NULL
//                and (t3.T_BLOCK_USER_ID is NULL OR v1.CUSTOMER_ID is not null)
//                and t4.T_BLOCK_CUSTOMER_ID is NULL
//                and t1.P_ICP_USER_ROLE_ID = :prmRoleID
//                {0}
//                ";

//            if (ICPUser.RoleId == (int)ICPUserRole.CAR || ICPUser.RoleId == (int)ICPUserRole.CARAdminForum)
//            {
//                sxQuery = String.Format(sxQuery, sxFilterHierarki_CAR);
//            }
//            else if (ICPUser.RoleId == (int)ICPUserRole.Sales)
//            {
//                sxQuery = String.Format(sxQuery, sxFilterHierarki_Sales);
//            }
//            else
//            {
//                sxQuery = String.Format(sxQuery, String.Empty);
//            }
            String sxExtraParam = String.Empty;
            if (ICPUser.RoleId == (int)ICPUserRole.CAR || ICPUser.RoleId == (int)ICPUserRole.CARAdminForum)
            {
                sxExtraParam = sxFilterHierarki_CAR3;
            }
            else if (ICPUser.RoleId == (int)ICPUserRole.Sales)
            {
                sxExtraParam = sxFilterHierarki_Sales3;
            }
            else
            {
                sxExtraParam = String.Empty;
            }
            String sxQuery = String.Format(
                @"
                SELECT   
                    t1.T_NEWS_FEED_ID,
                    t1.CONTENT,
                    t1.TITLE,
                    t1.POSTING_USER_ID,
                    v1.EMPLOYEE_NAME POSTING_USER_NAME,
                    t1.POSTED_USER_ID,
                    v2.EMPLOYEE_NAME POSTED_USER_NAME,
                    t1.CUSTOMER_ID,
                    t1.COMPANY_NAME,
                    m1.FEED_SOURCE_ID,
                    m1.NAME SOURCE_NAME,
                    m1.URL SOURCE_URL,
                    t1.POST_DATE,
                    COUNT (t3.T_FEED_COMMENT_ID) COMMENT_COUNT
               FROM 
                    (
                        select * from
                        (
                            select NF2.* from
                            (
                                select * from                        
                                (
                                    select t0.*, v3.SALES_EMPLOYEE_NO, v3.CAR_EMPLOYEE_NO, v3.COMPANY_NAME
                                    from 
                                        T_NEWS_FEED t0
                                        LEFT JOIN V_ICP_COMPANY_CAR_SALES v3
                                            ON t0.CUSTOMER_ID = v3.CUSTOMER_ID
                                    WHERE   NVL (t0.IS_DELETED, 'N') = 'N'                                    
                                    -- ditambah and buat OLDER dan UPDATE        
                                ) NF
                                -- jadi parameter hierarki : START
                                {0}
                                -- jadi parameter hierarki : END
                                order by NF.T_NEWS_FEED_ID desc
                            ) NF2
                            left join T_FEED_SUBSCRIPTION x1
                                on NF2.FEED_SOURCE_ID = x1.FEED_SOURCE_ID
                            left join T_FEED_UNSUBSCRIBE x2
                                on NF2.FEED_SOURCE_ID = x2.FEED_SOURCE_ID
                                and x2.ICP_USER_ID = :prmUID
                            left join T_BLOCK_USER x3
                                on NF2.POSTING_USER_ID = x3.BLOCKED_USER_ID
                                and x3.ICP_USER_ID = :prmUID
                            left join T_BLOCK_CUSTOMER x4
                                on NF2.CUSTOMER_ID = x4.CUSTOMER_ID
                                and x4.ICP_USER_ID = :prmUID
                            where
                                x1.P_ICP_USER_ROLE_ID = :prmRoleID
                                and x2.T_FEED_UNSUBSCRIBE_ID is NULL
                                and (x3.T_BLOCK_USER_ID is NULL OR NF2.CUSTOMER_ID is not null)
                                and x4.T_BLOCK_CUSTOMER_ID is NULL
                        )   
                    ) t1
                    LEFT JOIN
                    T_FEED_COMMENT t3
                    ON t1.T_NEWS_FEED_ID = t3.T_NEWS_FEED_ID
                    LEFT JOIN
                    FEED_SOURCE m1
                    ON t1.FEED_SOURCE_ID = m1.FEED_SOURCE_ID
                    LEFT JOIN
                    V_ICP_USER v1
                    ON t1.POSTING_USER_ID = v1.ICP_USER_ID
                    LEFT JOIN
                    V_ICP_USER v2
                    ON t1.POSTED_USER_ID = v2.ICP_USER_ID                                    
            GROUP BY   t1.T_NEWS_FEED_ID,
                      t1.CONTENT,
                      t1.TITLE,
                      t1.POSTING_USER_ID,
                      v1.EMPLOYEE_NAME,
                      t1.POSTED_USER_ID,
                      v2.EMPLOYEE_NAME,
                      t1.CUSTOMER_ID,
                      t1.COMPANY_NAME,
                      m1.FEED_SOURCE_ID,
                      m1.NAME,
                      m1.URL,
                      t1.POST_DATE
            ORDER BY   t1.T_NEWS_FEED_ID DESC",
            sxExtraParam);

            DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
			
            if (dt.Rows.Count > 0)
            {
                return ConvertTableToObjectArray(dt)[0];
            }
            else
            {
                return null;
            }
        }

        private NewsFeedData[] ConvertTableToObjectArray(DataTable dt)
        {
            NewsFeedData[] arrRet = new NewsFeedData[dt.Rows.Count];

            for (int i = 0; i < arrRet.Length; i++)
            {
                arrRet[i] = new NewsFeedData();

                arrRet[i].ID = dt.Rows[i].ReadInt("T_NEWS_FEED_ID");
                arrRet[i].sxContent = dt.Rows[i].ReadString("CONTENT");
                arrRet[i].sxTitle = dt.Rows[i].ReadString("TITLE");
                arrRet[i].sxUserId_Poster = dt.Rows[i].ReadString("POSTING_USER_ID");
                arrRet[i].sxUserName_Poster = dt.Rows[i].ReadString("POSTING_USER_NAME");
                arrRet[i].sxUserId_Target = dt.Rows[i].ReadString("POSTED_USER_ID");
                arrRet[i].sxUserName_Target = dt.Rows[i].ReadString("POSTED_USER_NAME");
                arrRet[i].iCustomerId = dt.Rows[i].ReadInt("CUSTOMER_ID");
                arrRet[i].sxCustomerName = dt.Rows[i].ReadString("COMPANY_NAME");
                arrRet[i].sxSourceName = dt.Rows[i].ReadString("SOURCE_NAME");
                arrRet[i].sxSourceURL = dt.Rows[i].ReadString("SOURCE_URL");
                arrRet[i].dtmDate = dt.Rows[i].ReadDateTime("POST_DATE");
                //dt.Rows[i]["NEWS_FEED_DATA"];
                //dt.Rows[i]["P_NEWS_FEED_DATA_TYPE_ID"];
                //dt.Rows[i]["NEWS_FEED_DATA_TYPE"];
                arrRet[i].iCommentCount = dt.Rows[i].ReadInt("COMMENT_COUNT");
            }

            return arrRet;
        }

        public override String GetCacheFilename()
        {
            return KCache.Filename.NEWS_FEED;
        }
    }
}